--SQL Hacks, Hack 18: Order Your Email
--sqlserver version

--------------
--   setup  --
--------------

CREATE TABLE email (e   VARCHAR(50));
INSERT INTO email VALUES ('I.Rankin@napier.ac.uk');
INSERT INTO email VALUES ('P.Bhardwaj@napier.ac.uk');
INSERT INTO email VALUES ('Scott.Kemmer@rbs.co.uk');
INSERT INTO email VALUES ('Alan.K.Buccannan@rbs.co.uk');
INSERT INTO email VALUES ('Complaints@sirius-cybernetics.com');
--------------------
--       hack     --
--------------------

SELECT * FROM email ORDER BY e;
SELECT SUBSTRING(e,1+CHARINDEX('@',e),50) AS domain
      ,SUBSTRING(e,1,CHARINDEX('@',e)-1)  AS account
 FROM email
ORDER BY domain, account
SELECT RIGHT(e,CHARINDEX('.', REVERSE(e))-1),e
  FROM email;
CREATE VIEW esort WITH SCHEMABINDING AS
 SELECT e,
        SUBSTRING(e,1+CHARINDEX('@',e),50) AS domain,
        SUBSTRING(e,2,50)                  AS tlDomain
         FROM dbo.email
GO
CREATE UNIQUE CLUSTERED INDEX uci ON esort(e);
GO
CREATE INDEX esortid1 ON esort(domain);
GO
CREATE INDEX esortid2 ON esort(tlDomain);
----------------
--    tidy    --
----------------

DROP TABLE email;
